package es.geodata.gvsig.extLinkedTables;

import java.awt.BorderLayout;
import java.awt.Color;
import java.awt.Font;
import java.awt.event.ActionEvent;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Properties;
import java.util.Vector;

import javax.swing.ImageIcon;
import javax.swing.JButton;
import javax.swing.JComponent;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableColumnModel;
import javax.swing.table.TableModel;

import org.apache.log4j.Logger;

import com.hardcode.gdbms.driver.exceptions.ReadDriverException;
import com.hardcode.gdbms.engine.values.Value;
import com.hardcode.gdbms.engine.values.ValueWriter;
import com.iver.andami.PluginServices;
import com.iver.andami.ui.mdiManager.WindowInfo;
import com.iver.cit.gvsig.FiltroExtension;
import com.iver.cit.gvsig.fmap.layers.FBitSet;
import com.iver.cit.gvsig.fmap.layers.FLyrVect;
import com.iver.cit.gvsig.fmap.layers.ReadableVectorial;
import com.iver.cit.gvsig.fmap.layers.SelectableDataSource;
import com.iver.cit.gvsig.fmap.layers.VectorialDBAdapter;
import com.iver.cit.gvsig.fmap.layers.VectorialFileAdapter;
import com.iver.cit.gvsig.fmap.layers.layerOperations.AlphanumericData;
import com.iver.cit.gvsig.project.documents.table.ProjectTable;
import com.iver.utiles.extensionPoints.ExtensionPoints;
import com.iver.utiles.extensionPoints.ExtensionPointsSingleton;
import com.jeta.forms.components.panel.FormPanel;
import com.jeta.forms.gui.common.FormException;

import es.icarto.gvsig.navtableforms.AbstractForm;
import es.udc.cartolab.gvsig.navtable.AbstractNavTable;
import es.udc.cartolab.gvsig.navtable.preferences.Preferences;


public class LinkedTablesForm extends AbstractForm {

	private static final long serialVersionUID = 1L;

	private FLyrVect layer;
	private ProjectTable ptMain;
	private ProjectTable ptLinked;
	private Properties properties;
	private String linkedName;
	private String dir;
	private FormPanel form;
	private JTable table;
	private JTable tree;
	private SelectableDataSource sds;
	private SelectableDataSource linkedsds;
	private String colNamesMain[];
	private int fieldIndex;
	private Connection connection;
	private JButton filterLinked = null;

	
	public LinkedTablesForm(FLyrVect layer, ProjectTable projectTable, ProjectTable linkedProjectTable, Properties prop) {

		super(layer);
		this.layer = layer;
		this.ptMain = projectTable;
		this.ptLinked = linkedProjectTable;
		this.properties = prop;
		this.linkedName = ptLinked.getName();
		if (linkedName.lastIndexOf(".") > 0) {
			this.linkedName = linkedName.substring(0, linkedName.lastIndexOf("."));
		}
		this.dir = getLayerDir(layer);
		initWindow();

	}

	// Initialize Window components
	private void initWindow() {

		// Set window width and height
		int height = Integer.parseInt(properties.getProperty("WINDOW_HEIGHT", "600").trim());
		int width = Integer.parseInt(properties.getProperty("WINDOW_WIDTH", "900").trim());
		viewInfo.setHeight(height);
		viewInfo.setWidth(width);
		viewInfo.setTitle(PluginServices.getText(LinkedTablesPreferences.CONFIG_PLUGIN, "window_title"));

		// Set labels
		form.getLabel("lbl_main").setText(PluginServices.getText(this, "lbl_main"));
		form.getLabel("lbl_linked").setText(PluginServices.getText(this, "lbl_linked"));
		form.getLabel("lbl_version").setText(PluginServices.getText(this, "lbl_version"));

	}


	// Initialize Main Table
	private void initMainTable() throws ReadDriverException {

		File file = getFileAlias(layer);
		sds = getRecordset();
		if (ptLinked != null) {
			linkedsds = getLinkedRecordset();
		}
		String fieldName = ptMain.getField1();
		fieldIndex = sds.getFieldIndexByName(fieldName);

		// Disable auto resizing
		table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);

		// Set font
		Font font = new Font("Tahoma", Font.PLAIN, 10);
		table.getTableHeader().setFont(font);

		// Get column fields
		colNamesMain = sds.getFieldNames();
		TableModel dataModel = new DefaultTableModel(1, colNamesMain.length);
		table.setModel(dataModel);

		// Set column headers.
		TableColumnModel columnModel = table.getColumnModel();
		String alias;
		for (int i = 0; i < colNamesMain.length; i++) {
			alias = getAlias(file, colNamesMain[i]);
			columnModel.getColumn(i).setHeaderValue(alias);
		}

		// If any feature selected check automatically 'Seleccionados'
		FBitSet bitset = sds.getSelection();
		if (bitset.length() > 0) {
			int currentPos = Long.valueOf(getPosition()).intValue();
			int pos = bitset.nextSetBit(currentPos + 1);
			if (pos != EMPTY_REGISTER) {
				setPosition(pos);
				setOnlySelected(true);
			}
		}

	}

	
	// Get Alias File of main table (it has to be a shape file)
	private File getFileAlias(FLyrVect layer) {

		File layerFile = null;
		String filePath = null;
		String pathToken = null;
		File fileAlias = null;

		ReadableVectorial source = layer.getSource();

		if (source != null && source instanceof VectorialFileAdapter) {
			layerFile = ((VectorialFileAdapter) source).getFile();
			filePath = layerFile.getAbsolutePath();
			pathToken = filePath.substring(0, filePath.lastIndexOf("."));
			fileAlias = new File(pathToken + ".alias");
			if (!fileAlias.exists()) {
				pathToken = Preferences.getAliasDir() + File.separator + layer.getName();
				pathToken = pathToken.substring(0, pathToken.lastIndexOf("."));
				fileAlias = new File(pathToken + ".alias");
			}
		} else if (source instanceof VectorialDBAdapter) {
			pathToken = Preferences.getAliasDir() + File.separator + layer.getName();
			pathToken = pathToken.substring(0, pathToken.lastIndexOf("."));
			fileAlias = new File(pathToken + ".alias");
		}

		return fileAlias;

	}

	
	// Get Alias of linked table (it has to be a dbf file)
	private File getFileAlias(String dirPath, String fileName) {

		String aux = dirPath + File.separator + fileName + ".alias";
		File fileAlias = new File(aux);
		if (!fileAlias.exists()) {
			aux = Preferences.getAliasDir() + File.separator + fileName + ".alias";
			fileAlias = new File(aux);
		}

		return fileAlias;

	}

	
	// Get Alias of selected field in the specified Alias file
	private String getAlias(File file, String fieldName) {

		String alias = fieldName;
		if (file.exists()) {
			try {
				String line;
				BufferedReader fileReader = new BufferedReader(new FileReader(file));
				while ((line = fileReader.readLine()) != null) {
					String tokens[] = line.split("=");
					if (tokens.length == 2) {
						String attrName = tokens[0].toUpperCase();
						if (fieldName.toUpperCase().compareTo(attrName) == 0) {
							alias = tokens[1];
							break;
						}
					}
				}
			} catch (FileNotFoundException e) {
				AbstractNavTable.logger.error(e.getMessage(), e);
			} catch (IOException e) {
				AbstractNavTable.logger.error(e.getMessage(), e);
			}
		}
		return alias;

	}

	
	// Initialize Linked Table
	private void initLinkedTable() throws ReadDriverException {

		try {
			String dbfConn = properties.getProperty("DBF_CONN");
			// String dbfDir = properties.getProperty("DBF_DIR");
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			String connString = "jdbc:odbc:Driver=" + dbfConn + ";DefaultDir=" + dir;
			connection = DriverManager.getConnection(connString);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
			LinkedTablesExtension.showError("error_dbf_dir", e.getMessage(), "app_title");
		}

	}

	
	// Retrieve Main Table data into JTable
	private void setMainTable() throws ReadDriverException {

		// Set row data
		Value[] row = sds.getRow(getPosition());
		for (int j = 0; j < colNamesMain.length; j++) {
			table.setValueAt(row[j], 0, j);
		}

	}

	// Retrieve Linked Table data into JTable
	private void setLinkedTable() throws ReadDriverException {

		String fieldValue = sds.getFieldValue(getPosition(), fieldIndex).getStringValue(ValueWriter.internalValueWriter);
		String sql = "";

		try {

			File file = getFileAlias(dir, linkedName);
			String fieldName2 = ptMain.getField2();
			sql = "SELECT * FROM [" + linkedName + "] WHERE " + fieldName2 + " = " + fieldValue + ";";
			System.out.println(sql);
			Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			ResultSet rs = stmt.executeQuery(sql);
			ResultSetMetaData rsmd = rs.getMetaData();

			// Disable auto resizing
			tree.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);

			// Set font
			Font font = new Font("Tahoma", Font.PLAIN, 10);
			tree.getTableHeader().setFont(font);

			// Get total rows and columns
			rs.last();
			int rcount = rs.getRow();
			int numColumns = rsmd.getColumnCount();
			TableModel dataModel = new DefaultTableModel(rcount, numColumns);
			tree.setModel(dataModel);

			// Set column headers
			TableColumnModel columnModel = tree.getColumnModel();
			String alias;
			for (int i = 1; i <= numColumns; i++) {
				alias = getAlias(file, rsmd.getColumnName(i));
				columnModel.getColumn(i - 1).setHeaderValue(alias);
			}

			// Set row data
			rs.first();
			for (int i = 0; i < rcount; i++) {
				for (int j = 1; j <= numColumns; j++) {
					tree.setValueAt(rs.getString(j), i, j - 1);
				}
				rs.next();
			}

		} catch (SQLException e) {
			e.printStackTrace();
			LinkedTablesExtension.showError("error_query", sql + "\n" + e.getMessage(), "app_title");
		}

	}

	
	// Get Recordset associated with Linked Table
	private SelectableDataSource getLinkedRecordset() {
		try {
			if (linkedsds == null && ptLinked != null) {
				AlphanumericData ad;
				ad = ptLinked.getAssociatedTable();
				if (ad != null) {
					linkedsds = ad.getRecordset();
				} else {
					linkedsds = ptLinked.getModelo().getRecordset();
				}
			}
		} catch (ReadDriverException e) {
			e.printStackTrace();
		}
		return linkedsds;
	}

	
	// Get Layer directory
	private String getLayerDir(FLyrVect layerV) {
		ReadableVectorial rv = layerV.getSource();
		if (rv instanceof VectorialFileAdapter) {
			VectorialFileAdapter vfa = (VectorialFileAdapter) rv;
			File file = vfa.getFile();
			System.out.println(file.getAbsolutePath());
			return file.getParent();
		}
		return null;
	}

	
	// Get Icon
	private JLabel getIcon(File iconPath) {
		ImageIcon logo = new ImageIcon(iconPath.getAbsolutePath());
		JLabel icon = new JLabel();
		icon.setIcon(logo);
		return icon;
	}

	
	// Get first row of NorthPanel to add header image
	private JPanel getNorthFirstRow() {
		File iconPath = getHeaderFile();
		JPanel northFirstRow = new JPanel(new BorderLayout());
		if (iconPath != null && iconPath.exists()) {
			northFirstRow.setBackground(Color.WHITE);
			northFirstRow.add(getIcon(iconPath), BorderLayout.WEST);
		}
		return northFirstRow;
	}
	
	
	@Override
	public WindowInfo getWindowInfo() {
		if (viewInfo == null) {
			viewInfo = new WindowInfo(WindowInfo.RESIZABLE | WindowInfo.ICONIFIABLE);
			viewInfo.setTitle(PluginServices.getText(LinkedTablesPreferences.CONFIG_PLUGIN, "window_title"));
		}
		return viewInfo;
	}
	
	@Override
	public FormPanel getFormBody() {
		if (form == null) {
			try {
				FileInputStream fis = new FileInputStream(new File(getXMLPath()));
				form = new FormPanel(fis);
			} catch (FileNotFoundException e) {
				e.printStackTrace();
			} catch (FormException e) {
				e.printStackTrace();
			}
		}
		return form;
	}

	@Override
	public String getXMLPath() {
		return LinkedTablesPreferences.XMLDATAFILE_PATH;
	}

	@Override
	public Logger getLoggerName() {
		return Logger.getLogger("LinkedTablesForm");
	}

	@Override
	protected void fillSpecificValues() {
		if (connection != null) {
			try {
				setMainTable();
				setLinkedTable();
			} catch (ReadDriverException rde) {
				rde.printStackTrace();
			}
		}
	}

	@Override
	protected void setListeners() {

		HashMap<String, JComponent> widgets = getWidgetComponents();
		super.setListeners();
		if (widgets.containsKey("table")) {
			table = (JTable) widgets.get("table");
		}
		if (widgets.containsKey("treetable")) {
			tree = (JTable) widgets.get("treetable");
		}
		try {
			initMainTable();
			initLinkedTable();
		} catch (ReadDriverException e) {
			e.printStackTrace();
		}

	}


	@Override
	protected Vector<?> checkChangedValues() {
		return null;
	}


	@Override
	protected void registerNavTableButtonsOnActionToolBarExtensionPoint() {

		ExtensionPoints extensionPoints = ExtensionPointsSingleton.getInstance();

		filterB = getNavTableButton(filterB, "/filter.png", "filterTooltip");
		extensionPoints.add(NAVTABLE_ACTIONS_TOOLBAR, "button-enable-filter", filterB);
		filterB.setBackground(new Color(255, 153, 153));

		noFilterB = getNavTableButton(noFilterB, "/nofilter.png", "noFilterTooltip");
		extensionPoints.add(NAVTABLE_ACTIONS_TOOLBAR, "button-disable-filter", noFilterB);

		filterLinked = getNavTableButton(filterLinked, "/filter.png", "filterLinked");
		extensionPoints.add(NAVTABLE_ACTIONS_TOOLBAR, "button-enable-filter-linked", filterLinked);
		filterLinked.setActionCommand("filter_linked");
		filterLinked.setBackground(new Color(153, 204, 255));

		zoomB = getNavTableButton(zoomB, "/zoom.png", "zoomButtonTooltip");
		extensionPoints.add(NAVTABLE_ACTIONS_TOOLBAR, "button-zoom", zoomB);

		selectionB = getNavTableButton(selectionB, "/Select.png", "selectionButtonTooltip");
		extensionPoints.add(NAVTABLE_ACTIONS_TOOLBAR, "button-selection", selectionB);

	}

	@Override
	protected JPanel getNorthPanel() {
		if (northPanel == null) {
			initNorthPanelButtons();
			northPanel = new JPanel(new BorderLayout());
			northPanel.add(getNorthFirstRow(), BorderLayout.NORTH);
			northPanel.add(getOptionsPanel(), BorderLayout.SOUTH);
		}
		return northPanel;
	}

	@Override
	public boolean isSavingValues() {
		return false;
	}

	@Override
	public void actionPerformed(ActionEvent e) {

		if (e.getActionCommand().equals("filter_linked")) {
			FiltroExtension fe = new FiltroExtension();
			fe.initialize();
			fe.setDatasource(getLinkedRecordset(), linkedName);
			fe.execute("FILTER_DATASOURCE");
			super.setOnlySelected(true);
		} else {
			super.actionPerformed(e);
		}

	}

	@Override
	public SelectableDataSource getRecordset() {
		try {
			if (sds == null) {
				sds = ptMain.getAssociatedTable().getRecordset();
			}
		} catch (ReadDriverException e) {
			e.printStackTrace();
		}
		return sds;
	}

}